#!/usr/bin/env python

"Reads an Apache log in the combined log format and imports it into a SQL database."

import os
format_string = os.environ.get('APACHE_LOG_FORMAT',
	'%V %h %l %u %t "%r" %>s %b "%{Referer}i" "%{User-Agent}i"')

import string
isdigit = string.digits.__contains__

from sys import stderr
debug_file = stderr
class FakeFile(object):
	write = len
#debug_file = FakeFile() # TEMP

################################################################################
# Consumer classes

# A Consumer object eats some portion of a log line. The LogReader maps format characters to Consumer objects.

import re

class Consumer(object):
	def consume(self, line):
		"Consumer.consume returns (consumed_value, rest_of_line), or None if the correct value isn't there to consume. Not implemented in Consumer, which is abstract."
		raise NotImplementedError, 'Consumer is an abstract class; you must subclass it and override consume'

	def cast(self, obj):
		"Returns obj, cast to the correct type. For example, NumberConsumer casts to int."
		return obj

class RegExConsumer(Consumer):
	"Consumes a match to a regex."
	exp = None
	def __init__(self, exp=None):
		if exp is not None:
			self.exp = re.compile(exp)
		assert self.exp is not None, '%r inited with no regular expression!' % (self,)
		def __repr__(self, exp=exp):
			return repr(exp)
		self.__repr__ = __repr__

	def consume(self, line):
		match = self.exp.match(line)
		if match:
			return (
				self.cast(match.group(0)),
				line[len(match.group(0)):]
				)
		else:
			return None

class NumberConsumer(RegExConsumer):
	"""Consumes decimal digits, optionally with a sign in front of them. If self.signed is true, the sign can be - as well as +.
Regex: /[-+]?[0-9]+/ (minus the - if signed is false)."""
	def __init__(self, signed=False):
		self.signed = signed
		self.exp = re.compile('[' + ('-' * bool(self.signed)) + '+]?[0-9]+')

	cast = int

def NumberConsumerFactory(signed=False, NumberConsumer=NumberConsumer, storage={}):
	signed = bool(signed)

	if signed not in storage:
		storage[signed] = NumberConsumer(signed)

	return storage[signed]
NumberConsumerFactory.__doc__ = NumberConsumer.__doc__
# Note: We can't clobbur NumberConsumer until we have created every subclass that needs it.
# NumberConsumer = NumberConsumerFactory

class MicrosecondsConsumer(NumberConsumer):
	"A NumberConsumer that divides the number by 1,000,000 using floating-point division."
	def cast(self, obj):
		return float(obj) / 1000000

class OneCharacterConsumer(Consumer):
	"Consumes exactly one of the given character. If the line does not start with that character, returns None."
	def __init__(self, char):
		self.char = char
	def consume(self, line):
		print >>debug_file, '%r: Looking for %r as start of %r' % (self, self.char, line)
		if line.startswith(self.char):
			print >>debug_file, '%r: Got it! Returning %r' % (self, (self.char, line[1:]))
			return (self.char, line[1:])
		else:
			print >>debug_file, '%r: 404 Not Found' % (self,)
			return None
	def __repr__(self):
		return '<OneCharacterConsumer: %r>' % (self.char,)
def OneCharacterConsumerFactory(char, OneCharacterConsumer=OneCharacterConsumer, storage={}):
	if char not in storage:
		storage[char] = OneCharacterConsumer(char)

	return storage[char]
OneCharacterConsumerFactory.__doc__ = OneCharacterConsumer.__doc__
OneCharacterConsumer = OneCharacterConsumerFactory

class UntilConsumer(Consumer):
	"Consumes everything up to, not including, the given character. If the line does not contain that character, returns None."
	def __init__(self, char):
		self.char = char
	def consume(self, line):
		print >>debug_file, '%r: Looking for %r in %r' % (self, self.char, line)
		try:
			idx = line.index(self.char)
		except ValueError:
			print >>debug_file, '%r: 404 Not Found' % (self,)
			return None
		else:
			print >>debug_file, '%r: Found at index %r' % (self, idx)
			return (line[:idx], line[idx:])
	def __repr__(self):
		return '<UntilConsumer: %r>' % (self.char,)
def UntilConsumerFactory(char, UntilConsumer=UntilConsumer, storage={}):
	if char not in storage:
		storage[char] = UntilConsumer(char)

	return storage[char]
UntilConsumerFactory.__doc__ = UntilConsumer.__doc__
UntilConsumer = UntilConsumerFactory

class IdentifierConsumer(RegExConsumer):
	exp = re.compile(r'-|[A-Za-z][-_A-Za-z0-9]*')
	def __init__(self):
		pass
	def __repr__(self):
		return '<IdentifierConsumer>'
def IdentifierConsumerFactory(IdentifierConsumer=IdentifierConsumer, storage={}):
	if 42 not in storage:
		storage[42] = IdentifierConsumer()

	return storage[42]
IdentifierConsumerFactory.__doc__ = IdentifierConsumer.__doc__
IdentifierConsumer = IdentifierConsumerFactory

class IPAddressConsumer(Consumer):
	"Consumes an IPv4 or IPv6 address."
	exp = re.compile('|'.join((
		r'([1-9]?[0-9]|1[0-9][0-9]|2[0-4][0-9]|25[0-5])\.([1-9]?[0-9]|1[0-9][0-9]|2[0-4][0-9]|25[0-5])\.([1-9]?[0-9]|1[0-9][0-9]|2[0-4][0-9]|25[0-5])\.([1-9]?[0-9]|1[0-9][0-9]|2[0-4][0-9]|25[0-5])',
		r'([0-9A-Fa-f]{1,4})?:(:[0-9A-Fa-f]{1,4}){0,7}'
		)))

class RequestConsumer(RegExConsumer):
	exp = re.compile('(GET|POST|HEAD|OPTIONS|PUT|DELETE) ([^ ]+)( HTTP/[0-9.]+)?')
	def __init__(self):
		pass

	def consume(self, line):
		match = self.exp.match(line)
		if match:
			return (
				self.cast(match.group(0)),
				line[len(match.group(0)):]
				)
		else:
			return None
def RequestConsumerFactory(RequestConsumer=RequestConsumer, storage={}):
	if 42 not in storage:
		storage[42] = RequestConsumer()

	return storage[42]
RequestConsumerFactory.__doc__ = RequestConsumer.__doc__
RequestConsumer = RequestConsumerFactory

class RequestMethodConsumer(RegExConsumer):
	exp = re.compile('GET|POST|HEAD|OPTIONS|PUT|DELETE')
	def __init__(self):
		pass
def RequestMethodConsumerFactory(RequestMethodConsumer=RequestMethodConsumer, storage={}):
	if 42 not in storage:
		storage[42] = RequestMethodConsumer()

	return storage[42]
RequestMethodConsumerFactory.__doc__ = RequestMethodConsumer.__doc__
RequestMethodConsumer = RequestMethodConsumerFactory

class QueryStringConsumer(RegExConsumer):
	"Matches a ? followed by zero or more spaces, or the empty string."
	exp = re.compile('\?[^ ]*|')
	def __init__(self):
		pass
def QueryStringConsumerFactory(QueryStringConsumer=QueryStringConsumer, storage={}):
	if 42 not in storage:
		storage[42] = QueryStringConsumer()

	return storage[42]
QueryStringConsumerFactory.__doc__ = QueryStringConsumer.__doc__
QueryStringConsumer = QueryStringConsumerFactory

class ProtocolConsumer(RegExConsumer):
	exp = re.compile('HTTP/[0-9.]+')
	def __init__(self):
		pass
def ProtocolConsumerFactory(ProtocolConsumer=ProtocolConsumer, storage={}):
	if 42 not in storage:
		storage[42] = ProtocolConsumer()

	return storage[42]
ProtocolConsumerFactory.__doc__ = ProtocolConsumer.__doc__
ProtocolConsumer = ProtocolConsumerFactory

class BytesSentConsumer(NumberConsumer):
	"The same as NumberConsumer, except that a plain '-' == 0. Used for %b."
	exp = re.compile('-|[0-9]+')
	def __init__(self):
		pass

	def consume(self, line):
		match = self.exp.match(line)
		if match:
			if match.group(0) == '-':
				return (0, line[1:])
			else:
				return super(self.__class__, self).consume(line)
		else:
			return None
def BytesSentConsumerFactory(BytesSentConsumer=BytesSentConsumer, storage={}):
	if 42 not in storage:
		storage[42] = BytesSentConsumer()

	return storage[42]
BytesSentConsumerFactory.__doc__ = BytesSentConsumer.__doc__
BytesSentConsumer = BytesSentConsumerFactory

NumberConsumer = NumberConsumerFactory

class ConnStatusConsumer(RegExConsumer):
	exp = re.compile('[-+X]')
	def __init__(self):
		pass
def ConnStatusConsumerFactory(ConnStatusConsumer=ConnStatusConsumer, storage={}):
	if 42 not in storage:
		storage[42] = ConnStatusConsumer()

	return storage[42]
ConnStatusConsumerFactory.__doc__ = ConnStatusConsumer.__doc__
ConnStatusConsumer = ConnStatusConsumerFactory

import time
class TimestampConsumer(RegExConsumer):
	chunks = {
		# strftime format specifiers, mapped to chunks of regex.
		'%': '%',
		'A': '(?P<weekday_name>Monday|Tuesday|Wednesday|Thursday|Friday|Saturday|Sunday)',
		'a': '(?P<weekday_name>Mon|Tue|Wed|Thu|Fri|Sat|Sun)',
		'B': '(?P<month_name>%s)' % ('|'.join((
			'January', 'February', 'March',
			'April',   'May',      'June',
			'July',    'August',   'September',
			'October', 'November', 'December',
		)),),
		'b': '(?P<month_name>%s)' % ('|'.join((
			'Jan', 'Feb', 'Mar',
			'Apr', 'May', 'Jun',
			'Jul', 'Aug', 'Sep',
			'Oct', 'Nov', 'Dec',
		)),),
		'C': '(?P<century>0?[0-9]+)',
		# %D: %m/%d/%y
		'd': '(?P<day_of_month>0[1-9]|[12][0-9]|3[01])',
		'e': '(?P<day_of_month> [1-9]|[12][0-9]|3[01])',
		# %F: %Y-%m-%d
		'G': '[0-9]{2,}',
		'g': '[0-9]{2}',
		'H': '(?P<hour>[01][0-9]|2[0-3])',
		# %h: %b
		'I': '(?P<hour_12-hour>0[0-9]|1[0-2])',
		'j': '(?P<day_of_year>[0-2][0-9]{2}|3[0-5][0-9]|36[0-6])',
		'k': '(?P<hour>[ 1][0-9]|2[0-3])',
		'l': '(?P<hour_12-hour> [1-9]|1[0-2])',
		'M': '(?P<minute>[0-5][0-9])',
		'm': '(?P<month>0[1-9]|1[0-2])',
		'n': '\n',
		'p': '(?P<ampm>[aApP][mM]|[aApP]\.[mM]\.?)',
		# %R: %H:%M
		# %r: %I:%M:%S %p
		'S': '(?P<second>[0-5][0-9]|60)',
		's': '(?P<seconds_since_Epoch>[0-9]+)',
		# %T: %H:%M:%S
		't': '\t',
		'U': '(?P<week_of_year_Sunday-based>[0-4][0-9]|5[0-3])',
		'u': '(?P<day_of_week>[1-7])',
		'V': '(?P<week_of_year_1-based>0[1-9]|[1-4][0-9]|5[0-3])',
		# %v: %e-%b-%Y
		'W': '(?P<week_of_year>[0-4][0-9]|5[0-3])',
		'w': '(?P<day_of_week_Sunday-based>[0-6])',
		'Y': '(?P<year>[0-9]+)',
		'y': '(?P<year_of_century>[0-9]{2,})',
		'z': '(?P<time_zone_offset>[-+][0-9]{4})',
	}
	chunks['D'] = '(%s)' % ('/'.join((chunks['m'], chunks['d'], chunks['y'])),)
	chunks['F'] = '(%s)' % ('-'.join((chunks['Y'], chunks['m'], chunks['d'])),)
	chunks['h'] = chunks['b']
	chunks['R'] = '(%s)' % (':'.join((chunks['H'], chunks['M'])),)
	chunks['r'] = '(%s)' % (':'.join((chunks['I'], chunks['M'], chunks['S'])) + chunks['p'],)
	chunks['T'] = '(%s)' % (':'.join((chunks['H'], chunks['m'], chunks['S'])),)
	chunks['v'] = '(%s)' % ('-'.join((chunks['e'], chunks['b'], chunks['Y'])),)

	def __init__(self, format=None):
		if format is None:
			format = '[%d/%b/%Y:%H:%M:%S %z]'
		self.format = format
		self.exp = re.compile(self.re_from_strftime(self.format))

	def re_from_strftime(self, format):
		result_chunks = []

		fmt_iter = iter(enumerate(format))
		for i, ch in fmt_iter:
			if ch == '%':
				j, ch2 = fmt_iter.next()
				result_chunks.append(self.chunks[ch2])
			else:
				result_chunks.append(re.escape(ch))

		print >>debug_file, 'Converted strftime format %r to regex:\n\t%s' % (format, ''.join(result_chunks))
		return ''.join(result_chunks)

	def cast(self, obj):
		# Python's time.strptime does not understand the BSD extension %z, which is required by CLF. Thus, we must implement it ourselves, using the regex.
		match = self.exp.match(obj)
		assert match, 'Regex %r (from strftime format %r) did not match time string %r' % (self.re_from_strftime(self.format), self.format, obj)

		try:
			seconds_since_Epoch = int(match.group('seconds_since_Epoch'))
		except IndexError:
			try:
				year = int(match.group('year'))
			except IndexError:
				year_of_century = int(match.group('year_of_century'))
				try:
					century = int(match.group('century'))
				except IndexError:
					print >>stderr, 'WARNING: Time format includes year, but not century - This is a Y2K problem!'
					if year >= 70:
						century = 19
					else:
						century = 20
				year = (century * 100) + year_of_century

			month_names = [
				'Jan', 'Feb', 'Mar',
				'Apr', 'May', 'Jun',
				'Jul', 'Aug', 'Sep',
				'Oct', 'Nov', 'Dec',
			]
			try:
				month = int(match.group('month'))
			except IndexError:
				# Get the month name (we hope), and determine the month number from that.
				month_name = match.group('month_name')[:3]
				assert len(month_name) == 3, 'Unrecognized month name %r' % (month_name,)
				month = month_names.index(month_name) + 1
			else:
				# We have the month number, so get the month name from that.
				month_name = month_names[month - 1]

			try:
				day_of_month = int(match.group('day_of_month'))
			except IndexError:
				day_of_year = int(match.group('day_of_year'))
				raise "I don't know what to do with these yet..."

			try:
				hour = int(match.group('hour'))
			except IndexError:
				# 12-hour hours start at 1, so we must subtract that 1 to have a zero-based hour.
				hour = int(match.group('hour_12-hour')) - 1
				try:
					ampm = int(match.group('ampm'))
				except IndexError:
					print >>stderr, 'WARNING: Time format %r uses 12-hour time, but does not include an AM/PM! I must assume that you really meant 24-hour time (which is %H or %k rather than %I or %l).'
				else:
					if ampm.lower().startswith('p'):
						hour += 12

			try:
				minute = int(match.group('minute'))
			except IndexError:
				minute = 0

			try:
				second = int(match.group('second'))
			except IndexError:
				second = 0

			try:
				time_zone_offset = match.group('time_zone_offset')
			except IndexError:
				# Assume UTC.
				import datetime
				dt = datetime.datetime(year, month, day_of_month, hour, minute, second)
				return dt.timetuple()

			# Man, this sucks.
			# Python has no facility to parse a time zone offset, except for email.Utils.parsedate. The format is slightly different, but adaptable.
			import email.utils
			# year, month, day_of_month, hour, minute, second
			try:
				weekday_name = match.group('weekday_name')[:3]
				assert len(weekday_name) == 3, 'Unrecognized weekday name %r' % (weekday_name,)
			except IndexError:
				# %u (BSD extension) uses 1-7 (starting on Monday) rather than 0-6 (starting on Sunday). A mod-7 quickly normalizes it to the 0-6 nature.
				try:
					weekday = int(match.group('day_of_week')) % 7
				except IndexError:
					import datetime
					dt = datetime.datetime(year, month, day_of_month, hour, minute, second)
					# dt.weekday() returns 0-6, but it starts on Monday, which means that it won't work with our list of weekday names.
					weekday = dt.isoweekday() % 7

				weekday_name = ['Sun', 'Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat'][weekday]

			rfc2822_date = '%s, %u %s %u %u:%u:%u %s' % (weekday_name, day_of_month, month_name, year, hour, minute, second, time_zone_offset)
			return email.utils.parsedate(rfc2822_date)

		else: #try: seconds_since_Epoch
			# Sweet! We can just convert this.
			return time.gmtime(seconds_since_Epoch)
def TimestampConsumerFactory(format=None, TimestampConsumer=TimestampConsumer, storage={}):
	if format not in storage:
		storage[format] = TimestampConsumer(format)

	return storage[format]
TimestampConsumerFactory.__doc__ = TimestampConsumer.__doc__
TimestampConsumer = TimestampConsumerFactory

class FQDNConsumer(RegExConsumer):
	exp = re.compile(r' |([a-z]([-a-z0-9]*[a-z0-9])?\.?)+', re.I)
	def __init__(self):
		pass
	def __repr__(self):
		return '<FQDNConsumer>'
def FQDNConsumerFactory(FQDNConsumer=FQDNConsumer, storage={}):
	if 42 not in storage:
		storage[42] = FQDNConsumer()

	return storage[42]
FQDNConsumerFactory.__doc__ = FQDNConsumer.__doc__
FQDNConsumer = FQDNConsumerFactory

################################################################################

# A regular expression that matches a format specifier.
format_spec_exp = re.compile('%(?P<CONDITIONS>!?([0-9]+,)*([0-9]+)?)({(?P<PARAMETER>.+?)})?(?P<REDIRECT_QUALIFIER>[<>])?(?P<FORMAT_SPEC_LETTER>[%a-zA-Z])')

class LogReader(object):
	def __init__(self, format):
		self.consumers = []
		self.keys = []

		fmt_iter = iter(enumerate(format))
		for i, ch in fmt_iter:
			if ch == '%':
				j, ch2 = fmt_iter.next()
				if ch2 == 'a':
					self.consumers.append(IPAddressConsumer())
					self.keys.append('remote_ip_address')
				elif ch2 == 'A':
					self.consumers.append(IPAddressConsumer())
					self.keys.append('local_ip_address')
				elif ch2 == 'b':
					# Bytes-sent with "-" = 0
					self.consumers.append(BytesSentConsumer())
					self.keys.append('bytes_sent')
				elif ch2 == 'B':
					self.consumers.append(NumberConsumer())
					self.keys.append('bytes_sent')
				elif ch2 == 'c' or ch2 == 'X':
					# %c is the 1.3 version.
					self.consumers.append(ConnStatusConsumer())
					self.keys.append('connection_status')
				# %D: See %T below
				elif ch2 == 'f':
					k = j + 1
					ch3 = format[k]
					assert ch3 != '%', "Can't parse filename followed by format specifier; a literal character is required"
					self.consumers.append(UntilConsumer(ch3))
					self.keys.append('filename')
				elif ch2 == 'h':
					k = j + 1
					ch3 = format[k]
					assert ch3 != '%', "Can't parse remote hostname followed by format specifier; a literal character is required"
					self.consumers.append(UntilConsumer(ch3))
					self.keys.append('remote_hostname')
				elif ch2 == 'H':
					self.consumers.append(ProtocolConsumer())
					self.keys.append('request_protocol')
				elif ch2 == 'l':
					# Note: "Identifier" does not refer to ident. I used it in the programming-language sense.
					self.consumers.append(IdentifierConsumer())
					self.keys.append('ident')
				elif ch2 == 'm':
					self.consumers.append(RequestMethodConsumer())
					self.keys.append('request_method')
				elif ch2 == 'p':
					self.consumers.append(NumberConsumer())
					self.keys.append('port')
				elif ch2 == 'P':
					self.consumers.append(NumberConsumer())
					self.keys.append('PID')
				elif ch2 == 'q':
					self.consumers.append(QueryStringConsumer())
					self.keys.append('query_string')
				elif ch2 == 'r':
					self.consumers.append(RequestConsumer())
					self.keys.append('request_first_line')
				elif ch2 == 's':
					self.consumers.append(NumberConsumer())
					self.keys.append('original_status')
				elif ch2 == '>':
					k, ch3 = fmt_iter.next()
					if ch3 == 's':
						self.consumers.append(NumberConsumer())
						self.keys.append('final_status')
				elif ch2 == '{':
					# '{' isn't a format character. The formatter takes an argument, which is between { and }.
					name_end = format.index('}', j)
					name = format[j + 1:name_end]
					# Peel these characters off of the iterator.
					for i in xrange(j + 1, name_end + 1):
						fmt_iter.next()

					# Get the actual format character.
					j, ch2 = fmt_iter.next()

					k = j + 1
					ch3 = format[k]

					if ch2 == 'e':
						assert ch3 != '%', "Can't parse env variable followed by format specifier; a literal character is required"
						self.consumers.append(UntilConsumer(ch3))
						self.keys.append('env:' + name)
					elif ch2 == 'i':
						assert ch3 != '%', "Can't parse request header followed by format specifier; a literal character is required"
						self.consumers.append(UntilConsumer(ch3))
						self.keys.append('request_header:' + name)
					elif ch2 == 'n':
						# A note left under the given name by some module.
						assert ch3 != '%', "Can't parse note followed by format specifier; a literal character is required"
						self.consumers.append(UntilConsumer(ch3))
						self.keys.append('note:' + name)
					elif ch2 == 'o':
						assert ch3 != '%', "Can't parse response header followed by format specifier; a literal character is required"
						self.consumers.append(UntilConsumer(ch3))
						self.keys.append('response_header:' + name)
					elif ch2 == 't':
						# Don't append the format to this key.
						# In this case, name is the timestamp format.
						self.consumers.append(TimestampConsumer(name))
						self.keys.append('timestamp')
						# XXX What should happen when multiple timestamps collide?
				elif ch2 == 't':
					self.consumers.append(TimestampConsumer())
					self.keys.append('timestamp')
				elif ch2 == 'T':
					self.consumers.append(NumberConsumer())
					self.keys.append('time_taken')
				elif ch2 == 'D':
					# Same as %T, but in microseconds.
					self.consumers.append(MicrosecondsConsumer())
					self.keys.append('time_taken')
				elif ch2 == 'u':
					self.consumers.append(IdentifierConsumer())
					self.keys.append('remote_user')
				elif ch2 == 'U':
					k = j + 1
					ch3 = format[k]
					assert ch3 != '%', "Can't parse pathname followed by format specifier; a literal character is required"
					self.consumers.append(UntilConsumer(ch3))
					self.keys.append('pathname')
				elif ch2 == 'v':
					self.consumers.append(FQDNConsumer())
					self.keys.append('canonical_server_name')
				elif ch2 == 'V':
					# Maybe-canonical server name. (Depends on UseCanonicalName.)
					self.consumers.append(FQDNConsumer())
					self.keys.append('server_name')
				# %X: See %c above
				elif ch2 == 'I':
					self.consumers.append(NumberConsumer())
					self.keys.append('bytes_received_including_headers')
				elif ch2 == 'O':
					self.consumers.append(NumberConsumer())
					self.keys.append('bytes_sent_including_headers')
			else:
				self.consumers.append(OneCharacterConsumer(ch))
				self.keys.append(None)

	def parse_log_line(self, line):
		print >>debug_file, '*** Parsing line:', repr(line)
		matcher_idx = 0
		values = {}
		while line:
			try:
				matcher = self.consumers[matcher_idx]
			except IndexError:
				break
			assert matcher_idx < len(self.keys), "LogReader ran out of data keys at index %u; keys are %r" % (matcher_idx, self.keys)
			key = self.keys[matcher_idx]

			return_value = matcher.consume(line)

			if key is not None:
				assert key not in values, 'Key already detected: %r (values so far: %r)' % (key, values)
			try:
				values[key], line = return_value
			except TypeError:
				print >>stderr, 'Matcher %r could not consume value for key %r from %r' % (matcher, key, line)
				return None
			else:
				print >>debug_file, 'Value for key %r: %r' % (key, values[key])
				print >>debug_file, 'Remainder of line:', repr(line)

			matcher_idx += 1

		if None in values:
			del values[None] # Because that just wouldn't make sense.
		return values

type_bigint      = 'BIGINT'
type_int         = 'INT'
type_smallint    = 'SMALLINT'
type_real        = 'REAL'
type_varchar255  = 'VARCHAR(255)'
type_varchar1023 = 'VARCHAR(1023)'
type_char1       = 'CHAR(1)'
type_timestamp   = 'TIMESTAMP'

class SQLGenerator(object):
	"An SQL-generator object is responsible for the generation of SQL statements for a given row in a log file. You can subclass this class to tailor the generated statements to a specific SQL implementation."

	def __init__(self, table_name, schema):
		"schema is a sequence of strings representing column names."
		assert isinstance(table_name, str), 'SQLGenerator inited with non-string table name: ' + repr(table_name)
		assert iter(schema), 'SQLGenerator inited with non-iterable schema: ' + repr(schema)
		self.table_name = table_name
		self.schema = list(schema)

	# WARNING! Long method!
	def column_name_to_type(self, column_name):
		'Map a column name to the appropriate SQL type for that column. Override if your SQL implementation provides a more suitable type for a specific column (for example, PostgreSQL provides an "inet" type for IP addresses).'
		_column_types = {
			'bytes_received_including_headers': type_bigint,
			'bytes_sent'                      : type_bigint,
			'bytes_sent_including_headers'    : type_bigint,
			'PID'                             : type_int,
			'original_status'                 : type_smallint,
			'final_status'                    : type_smallint,
			'port'                            : type_smallint,
			'time_taken'                      : type_real,
			'local_ip_address'                : type_varchar255,
			'remote_ip_address'               : type_varchar255,
			'canonical_server_name'           : type_varchar255,
			'filename'                        : type_varchar255,
			'ident'                           : type_varchar255,
			'remote_hostname'                 : type_varchar255,
			'remote_user'                     : type_varchar255,
			'request_method'                  : type_varchar255,
			'request_protocol'                : type_varchar255,
			'server_name'                     : type_varchar255,
			'pathname'                        : type_varchar1023,
			'query_string'                    : type_varchar1023,
			'request_first_line'              : type_varchar1023,
			'connection_status'               : type_char1,
			'timestamp'                       : type_timestamp,
		}
		if column_name in _column_types:
			return _column_types[column_name]
		elif column_name.startswith('env:')          \
		or   column_name.startswith('note:')          \
		or   column_name.startswith('request_header:') \
		or   column_name.startswith('response_header:'):
			return type_varchar1023
		else:
			raise ValueError, 'Unrecognized column name %r for selection of type' % (column_name,)

	def to_column_name(self, column_name):
		"Return a valid column name generated from the given column name." \
		"" \
		"This implementation generates a quoted identifier from the string."
		return '"%s"' % (column_name.replace('"', '""'),)

	def to_literal_string(self, string):
		"Convert a Python string to a valid SQL string literal." \
		"" \
		"This is done by wrapping it in '' and converting all 's within the string to a '' pair."
		assert isinstance(string, str), "Can't convert non-string to string literal (alleged string is %r)" % (string,)
		return "'%s'" % (column_name.replace("'", "''"),)

	def to_literal(self, row, column_name):
		"Return a SQL literal for the value in row[column_name]. This value should be suitable for concatenation into a SQL statement. You are welcome to use the column name to determine appropriate conversion. The return value must be a string."
		import time
		element = row[column_name]
		if isinstance(element, time.struct_time):
			return "TIMESTAMP '%u-%u-%u %u:%u:%u'" % element
		else:
			# Well, that's it for discovery by Python type. Now we need to sort by column name.
			if column_name in (
				'bytes_received_including_headers', 'bytes_sent', 'bytes_sent_including_headers', # BIGINT
				'PID', #INT
				'original_status', 'final_status', 'port', #SMALLINT
				'time_taken', #REAL
			):
				return repr(element) # Mainly only important for REAL (float).

			elif column_name in (
				'canonical_server_name', 'filename', 'ident', 'remote_hostname', 'remote_user', 'request_method', 'request_protocol', 'server_name', #VARCHAR(255)
				'pathname', 'query_string', 'request_first_line', 'connection_status', #VARCHAR(1023)
			):
				return self.to_literal_string(element)

			elif column_name.startswith('env:')          \
			or   column_name.startswith('note:')          \
			or   column_name.startswith('request_header:') \
			or   column_name.startswith('response_header:'):
				return self.to_literal_string(element)

			else:
				raise ValueError, 'Unrecognized value %r for column %r; unable to map it to a type name' % (element, column_name)

	def generate_SQL_insert(self, row):
		"Generates an INSERT statement for the given row. The row is a dictionary containing all the data in the row."
		columns = []
		elements = []
		for column_name in self.schema:
			columns.append(self.to_column_name(column_name))
			element = row[column_name]
			elements.append(self.to_literal(element))
		return 'INSERT INTO %s (%s) VALUES (%s);' % (self.table_name, ', '.join(columns), ', '.join(elements))

if __name__ == '__main__':
	reader = LogReader(format_string)

	print >>debug_file, 'format_string:', repr(format_string)
	print >>debug_file
	from itertools import izip as zip
	for key, consumer in zip(reader.keys, reader.consumers):
		if key is None: key = ''
		print >>debug_file, '%s\t%r' % (key, consumer)
	print >>debug_file

	import fileinput
	for line in fileinput.input(openhook=fileinput.hook_compressed):
		print 'OUTPUT:', reader.parse_log_line(line) # TEMP
